package com.example.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.UUID;
import javax.jws.soap.SOAPBinding.Use;
import com.example.dao2.UserDao;
import com.example.dao2.VehicleDao;
import com.example.debug.gson.DaoDebug;
import com.example.entity.User;
import com.example.entity.Vehicle;
import com.example.entity.VehicleFaultInfo;
import com.example.utils.DButil;
import com.google.gson.Gson;
import com.mysql.jdbc.PreparedStatement;
/**
 * Vehicle表Dao层的实现类 
 * @author Roy
 *
 */
public class VehicleDaoImpl3  implements  VehicleDao{	
/**
 * 向vehicleinfotbl表中插入一条数据
 */
	public Boolean insert(VehicleFaultInfo v) {
		String sql="insert into vehiclerecordtbl(id,name,idno,vno,createTime,faultRecord,penalty,dealid)values(?,?,?,?,?,?,?,?)";
		DButil dbUtil=new DButil();
	   Connection connect=	dbUtil.openConnection();
		try {
			java.sql.PreparedStatement pstmt=connect.prepareStatement(sql);
			pstmt.setString(1, v.getId());
			pstmt.setString(2,v.getName());
			pstmt.setString(3,v.getIdno());
			pstmt.setString(4,v.getVno());
			pstmt.setString(5,v.getCreatetime());
			pstmt.setString(6,v.getFaultRecord());
			pstmt.setString(7,v.getPenalty());
			pstmt.setString(8,v.getDealid());
		//	ResultSet rs=pstmt.executeQuery();
			if (pstmt.executeUpdate()>0) {
				
				return true;	
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("insert into vehicleinfotbl has error: "+e.toString());
			e.printStackTrace();
		}finally{
			dbUtil.closeConn(connect);
		 }
		// TODO Auto-generated method stub
		return false;
	}

/**
 * 查询车辆
 */
	public String query(Vehicle v) {
		ResultSet rs = null;
		String search = "";
		DButil dbUtil = new DButil();
		Connection connect = dbUtil.openConnection();
		try {
			/**
			 * 以下是大篇幅的拼接sql语句
			 */

			if (v.getVno() == null || v.getVno().equals("")) {
				search += "  AND 1 =? ";
			} else {
				search += "   AND A.vno =?";
			}
			if (v.getIdno() == null || v.getIdno().equals("")) {
				search += "  AND 1 =? ";
			} else {
				search += "  AND A.idno =? ";
			}
			if (v.getType() == null || v.getType().equals("")) {
				search += "  AND 1 =? ";
			} else {
				search += "  AND A.type LIKE ? ";
			}
			if (v.getProduct() == null || v.getProduct().equals("")) {
				search += "  AND 1 =? ";
			} else {
				search += " AND  A.product LIKE ?";
			}
			if (v.getColor() == null || v.getColor().equals("")) {
				search += "  AND 1 =? ";
			} else {
				search += "  AND  A.color LIKE ?  ";
			}
			String query = "SELECT * from (select * from vehicletbl A WHERE  1 = 1 "
					+ search
					+ ") C left join vehiclerecordtbl B	on C.vno = B.vno";
			System.out.println("query --" + query);
			java.sql.PreparedStatement pstmt = connect.prepareStatement(query);

			System.out.println("query是------" + query);
			/**
			 * 以下是拼接查询条件
			 */
			if (v.getVno() == null || v.getVno().equals("")) {
				pstmt.setString(1, "1");
			} else {
				pstmt.setString(1, v.getVno());
			}
			if (v.getIdno() == null || v.getIdno().equals("")) {
				pstmt.setString(2, "1");
			} else {
				pstmt.setString(2, v.getIdno());
			}
			if (v.getType() == null || v.getType().equals("")) {
				pstmt.setString(3, "1");
			} else {
				pstmt.setString(3, "%" + v.getType() + "%");
			}
			if (v.getProduct() == null || v.getProduct().equals("")) {
				pstmt.setString(4, "1");
			} else {
				pstmt.setString(4, "%" + v.getProduct() + "%");
			}
			if (v.getColor() == null || v.getColor().equals("")) {
				pstmt.setString(5, "1");
			} else {
				pstmt.setString(5, "%" + v.getColor() + "%");
			}
			rs = pstmt.executeQuery();
			//return rs;
			ArrayList<Vehicle> vs = new ArrayList<Vehicle>();
			ArrayList<VehicleFaultInfo> vcfis = new ArrayList<VehicleFaultInfo>();
			while (rs.next()) {
				//如果fault中有两条记录，导致查询出来的vehicle也存在两条记录，这是多余的,无法解决！
			  Vehicle v2 = new Vehicle();
				VehicleFaultInfo vfi = new VehicleFaultInfo();
				v2.setId(rs.getObject(1).toString());
				v2.setVno(rs.getObject(2).toString());
				v2.setProduct(rs.getObject(3).toString());
				v2.setIdno(rs.getObject(4).toString());
				v2.setType(rs.getObject(5).toString());
				v2.setCreateTime(rs.getObject(6).toString());
				v2.setColor(rs.getObject(7).toString());
				if (rs.getObject(8) != null) {
					v2.setPicUrl(rs.getObject(8).toString());
                	}
				if (rs.getObject(9) != null) {
					// 第9行开始是明细表的内容
					vfi.setId(rs.getObject(9).toString());
					vfi.setName(rs.getObject(10).toString());
					vfi.setIdno(rs.getObject(11).toString());
					vfi.setVno(rs.getObject(12).toString());
					vfi.setCreatetime(rs.getObject(13).toString());
					vfi.setFaultRecord(rs.getObject(14).toString());
					vfi.setPenalty(rs.getObject(15).toString());
					vfi.setDealid(rs.getObject(16).toString());
					vcfis.add(vfi);
					v2.setRecords(vcfis);
				}
				vs.add(v2);
			}
			System.out.println("vs is ----" + vs.toString());
			rs.last();
			int rowCount = rs.getRow(); // 获得ResultSet的总行数
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			Gson gson = new Gson();
			String s = gson.toJson(vs);
			System.out.println(s);
			System.out.println("rs行数是" + rowCount + "rs的列数是" + columnCount);
			return s;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("query from vehicleinfotbl has error: "
					+ e.toString());
			e.printStackTrace();
			
			
			
		}finally{
			dbUtil.closeConn(connect);
			
		}
		// TODO Auto-generated method stub
		return "";
	}
	
	public static void main(String[] args) {
		
		Vehicle v=new Vehicle();
		 v.setColor("黑色");
          v.setType("卡车");
		//v.setVno("鲁W7788");
		v.setProduct("大众");
		
	    String result=		new VehicleDaoImpl().query(v);
	    System.out.println("----result is ---"+result);
	
	}
}
